/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.uta.tsrh.specialnotes.misc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author David
 */
public class SQLMgr implements DBMgr {
    
    
    private Connection connection = null;
    
    public SQLMgr() {
        openDB();
    }

    private void openDB() {
        if (this.connection == null) {
            connection = SQLConnMgr.getConnection();
        }
    }
    
    @Override
    public int addPatientNote(String patientId, PatientNote note) {
        PreparedStatement statement;
        int newId = 0;
        try {
            statement = connection.prepareStatement(
                    "INSERT INTO PatientNote ("
                    + "patientId,"
                    + "date,"
                    + "title,"
                    + "source,"
                    + "content"
                    + ") "
                    + "VALUES (?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, patientId);
            statement.setTimestamp(2, note.getDate());
            statement.setString(3, note.getTitle());
            statement.setString(4, note.getSource());
            statement.setString(5, note.getContent());
            
            statement.executeUpdate();
            
            ResultSet keys = statement.getGeneratedKeys();
            if (keys.next()) {
                newId = keys.getInt(1);
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }
        return newId;
    }
    
    @Override
    public void editPatientNote(PatientNote note) {
        Statement statement;
        try {
            statement = connection.prepareStatement(
                    "UPDATE PatientNote SET "
                    + "title = ?, "
                    + "content = ? "
                    + "WHERE "
                    + "id = ?");
            ((PreparedStatement)statement).setString(1, note.getTitle());
            ((PreparedStatement)statement).setString(2, note.getContent());
            ((PreparedStatement)statement).setInt(3, note.getId());
          
            ((PreparedStatement)statement).executeUpdate();
            
        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    

    @Override
    public int addPatientPhoneCall(String patientId, PatientPhoneCall call) {
        PreparedStatement statement;
        int newId = 0;
        try {
            statement = connection.prepareStatement(
                    "INSERT INTO PatientPhoneCall ("
                    + "patientId,"
                    + "title,"
                    + "date,"
                    + "caller,"
                    + "sourceNumber,"
                    + "content"
                    + ") "
                    + "VALUES (?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, patientId);
            statement.setString(2, call.getTitle());
            statement.setTimestamp(3, call.getDate());
            statement.setString(4, call.getCaller());
            statement.setString(5, call.getSourceNumber());
            statement.setString(6, call.getContent());
            
            statement.executeUpdate();
            
            ResultSet keys = statement.getGeneratedKeys();
            if (keys.next()) {
                newId = keys.getInt(1);
            }
            
            
            
        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }
        return newId;
    }

    @Override
    public void hidePatientNote(int noteId) {
        Statement statement;
        try {
            statement = connection.prepareStatement("UPDATE PatientNote SET hidden = 1 WHERE "+
              "id = ?");
            ((PreparedStatement)statement).setInt(1, noteId);
            ((PreparedStatement)statement).executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void hidePatientPhoneCall(int callId) {
        Statement statement;
        try {
            statement = connection.prepareStatement("UPDATE PatientPhoneCall SET hidden = 1 WHERE "+
              "id = ?");
            ((PreparedStatement)statement).setInt(1, callId);
            ((PreparedStatement)statement).executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public String getNewId(String firstName, String lastName) {
        Statement statement;
        try {
            
            
            String fL = String.valueOf(firstName.charAt(0));
            String lL = String.valueOf(lastName.charAt(0));
            statement = connection.prepareStatement("SELECT patientId FROM PatientRecord WHERE "+
              "patientId LIKE \""+fL+lL+"%\" ORDER BY patientId DESC");
            
            ResultSet results = ((PreparedStatement)statement).executeQuery();
            
            Integer max = 0;
            while (results.next()) {
                String lastId = results.getString("patientId");
                Integer number = Integer.parseInt(lastId.substring(2));
                if (number > max) {
                    max = number;
                }
            }
            max++;
            return fL+lL+max;
            
        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
    
    /**
     * Get patient record by patient id
     * @param patientId
     * @return 
     */
    @Override
    public PatientRecord getPatientRecord(String patientId) {
              
        PatientRecord newPR = null;
        try {
            
            ResultSet results = getPatientRecordResults(patientId);
            
            while (results.next()) {
                int addrId = results.getInt("address");
                
                newPR = new PatientRecord(
                        results.getString("firstName"),
                        results.getString("lastName"),
                        results.getString("patientId"),
                        results.getTimestamp("dob"),
                        results.getString("contactPhone"),
                        getAddress(addrId),
                        results.getString("mothersName")
                        );
            }
            
            results = getPatientPhoneCalls(patientId);
            
            while(results.next()) {
                
                PatientPhoneCall newPC = new PatientPhoneCall(
                        results.getInt("id"),
                        results.getString("title"),
                        results.getTimestamp("date"),
                        results.getString("caller"),
                        results.getString("sourceNumber"),
                        results.getString("content")
                        );
                newPR.addPhoneCall(newPC);
            }
            
            results = getPatientNotes(patientId);
            
            while(results.next()) {
                
                PatientNote newPN = new PatientNote(
                        results.getInt("id"),
                        results.getTimestamp("date"),
                        results.getString("title"),
                        results.getString("source"),
                        results.getString("content")
                        );
                newPR.addNote(newPN);
            }
            
            
        
        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }
        return newPR;
    }
    
    private ResultSet getPatientRecordResults(String patientId) throws SQLException {
        Statement statement = connection.prepareStatement("SELECT * FROM PatientRecord WHERE "+
                    "patientId = ?");
        ((PreparedStatement)statement).setString(1, patientId);
        ResultSet results = ((PreparedStatement)statement).executeQuery();
        return results;
    }
    private ResultSet getPatientPhoneCalls(String patientId) throws SQLException {
        Statement statement = connection.prepareStatement("SELECT * FROM PatientPhoneCall WHERE "+
                    "patientId = ? AND hidden = 0");
        ((PreparedStatement)statement).setString(1, patientId);
        ResultSet results = ((PreparedStatement)statement).executeQuery();
        return results;
    }
    private ResultSet getPatientNotes(String patientId) throws SQLException {
        Statement statement = connection.prepareStatement("SELECT * FROM PatientNote WHERE "+
                    "patientId = ? AND hidden = 0");
        ((PreparedStatement)statement).setString(1, patientId);
        ResultSet results = ((PreparedStatement)statement).executeQuery();
        return results;
    }
    private Address getAddress(int addressId) {
        return null;
    }

    /**
     * Get patient phone call by call id
     * @param callId
     * @return 
     */
    @Override
    public PatientPhoneCall getPatientPhoneCall(int callId) {
        PatientPhoneCall newPC = null;
        try {
            ResultSet results = getPatientPhoneCallResults(callId);
            
            while (results.next()) {

                newPC = new PatientPhoneCall(
                            results.getInt("id"),
                            results.getString("title"),
                            results.getTimestamp("date"),
                            results.getString("caller"),
                            results.getString("sourceNumber"),
                            results.getString("content")
                            );
            }
            
            
        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        
        return newPC;
        
    }
    
    private ResultSet getPatientPhoneCallResults(int callId) throws SQLException {
        Statement statement = connection.prepareStatement("SELECT * FROM PatientPhoneCall WHERE "+
                    "id = ?");
        ((PreparedStatement)statement).setInt(1, callId);
        ResultSet results = ((PreparedStatement)statement).executeQuery();
        return results;
    }

    @Override
    public PatientNote getPatientNote(int noteId) {
        
        PatientNote newPN = null;
        try {
            ResultSet results = getPatientNoteResults(noteId);
            
            while (results.next()) {

                newPN = new PatientNote(
                            results.getInt("id"),
                            results.getTimestamp("date"),
                            results.getString("title"),
                            results.getString("source"),
                            results.getString("content")
                            );
            }
            
            
        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        
        return newPN;
    }
    
    private ResultSet getPatientNoteResults(int noteId) throws SQLException {
        Statement statement = connection.prepareStatement("SELECT * FROM PatientNote WHERE "+
                    "id = ?");
        ((PreparedStatement)statement).setInt(1, noteId);
        ResultSet results = ((PreparedStatement)statement).executeQuery();
        return results;
    }

    @Override
    public boolean ready() {
        return connection != null;
    }
    
    
    
}
